Release 10.1A: OpenEdge Development:
Progress 4GL Reference

DEFINE QUERY statement

Defines a query that can be opened with an OPEN QUERY statement and from which records can be retrieved with a GET statement or BROWSE widget.

Syntax

DEFINE [ [ NEW ] SHARED ] [ PRIVATE | PROTECTED ] QUERY query FOR bufname [ field-list ] [ , bufname [ field-list ] ] ... [ CACHE n ] [ SCROLLING ] [ RCODE-INFORMATION ]

NEW SHARED QUERY query

Defines and identifies a query to be shared with one or more procedures called directly or indirectly by the current procedure. The called procedures must define the same query name as SHARED. For shared queries, each bufname must be the name of a shared buffer. The shared buffers must be specified in the same order both across shared queries and in the OPEN QUERY.

SHARED QUERY query

Defines and identifies a query that was initially defined by another procedure as NEW SHARED. For shared queries, each bufname must be the name of a shared buffer. The shared buffers must be specified in the same order across shared queries and in the OPEN QUERY.

[ PRIVATE | PROTECTED ] QUERY query

Defines and identifies a query as a data member for a class, and optionally specifies an access mode for that data member. Do not specify an access mode when defining a query for a method within a class.

PRIVATE data members can be accessed only by the defining class. PROTECTED data members can be accessed by the defining class and any of its inheriting classes. The default access mode is PRIVATE.

Note: These options are applicable only when defining a data member for a class in a class definition (.cls) file.

QUERY query

Identifies the name of the query you are defining. You can define the query in a procedure or a method within a class.

FOR bufname [ field-list ] [ , bufname [ field-list ] ] ...

Specifies the buffers to be used by the query, where bufname is a table or alternate buffer name. For a shared query, each bufname must be a shared buffer. If the query definition references more than one buffer, it defines a join.

Once the query has been defined, you cannot change the buffers that it references, even if the query is closed and re-opened. For example, a buffer, buff1, is created for the customer table in a DEFINE QUERY or OPEN QUERY for the query, qry1. The query is run and closed. You cannot now DEFINE or OPEN qry1 with buff1 for the item table. You can reuse buffers with CREATE QUERY, but you must re-run QUERY-PREPARE.

The field-list specifies a list of fields to include or exclude when you open the query. This is the syntax for field-list:

{ FIELDS [ ( [ field ... ] ) ] | EXCEPT [ ( [ field ... ] ) ] }

The FIELDS option specifies the fields you want to include in the query, and the EXCEPT option specifies the fields that you want to exclude from the query. The field parameter is the name of a single field in the table specified by bufname. If field is an array reference, the whole array is retrieved even if only one element is specified. Specifying FIELDS with no field references causes Progress to retrieve sufficient information to extract the ROWID value for each record in the query (returnable using the ROWID function). Specifying EXCEPT with no field references or specifying bufname without a field-list causes Progress to retrieve all fields for each record in the query.

This statement defines a query to retrieve only the name and balance fields from the customer table:

DEFINE QUERY custq FOR customer FIELDS (name balance).

This statement defines a query to retrieve all fields of the customer table except the name and balance fields:

DEFINE QUERY custq FOR customer EXCEPT (name balance).

When you specify a field list for a query, Progress might retrieve additional fields or complete records depending on the type of query operation and the DataServer that provides the records. Thus, Progress:

Retrieves any additional fields required by the client to complete the record selection.
Retrieves complete records when you open the query with EXCLUSIVE-LOCK or update any row (such as with a browse). This ensures proper operation of updates and the local before-image (BI) file. For information on the local BI file, see OpenEdge Data Management: Database Administration .
Retrieves complete records for DataServers that do not support SHARE-LOCK. For more information, see the OpenEdge DataServer Guides, OpenEdge Data Management: DataServer for Microsoft SQL Server , OpenEdge Data Management: DataServer for ODBC , and OpenEdge Data Management: DataServer for ORACLE .
Note: Always specify fields that you plan to reference in the field list. Only those extra fields that the client requires for record selection are added to the specified field list. Progress distributes record selection between the client and server depending on a number of factors that change with each OpenEdge release. Therefore, never rely on fields that you did not specify but which Progress fetches for its own needs; they might not always be available. There is no additional cost to specify a field in the list that you otherwise expect Progress to provide.

This query example retrieves the customer.cust-num field in addition to those specified in the field lists because it is required to satisfy the inner join between the customer and order tables.

r-qryjoin.p

DEFINE QUERY coq FOR customer FIELDS (name), order FIELDS (order-num sales-rep). OPEN QUERY coq FOR EACH customer, EACH order OF customer. REPEAT: GET NEXT coq. IF NOT AVAILABLE (order) THEN LEAVE. DISPLAY customer.name customer.cust-num order.order-num order.sales-rep WITH FRAME q-frame 10 DOWN. END.

However, do not rely on Progress to always provide such extra fields. For reliability, add the cust-num field to the customer field list. For example:

DEFINE QUERY coq FOR customer FIELDS (name cust-num), order FIELDS (order-num sales-rep). . . .

When you specify a field list in a shared query, you must specify the complete field list in the NEW SHARED query definition. Each corresponding SHARED query definition in another procedure file (.p) requires only the FIELDS or EXCEPT keywords, but can also include empty parentheses or the complete field list with no difference in functionality.

You can match this NEW SHARED query definition for customer with any of the following SHARED query definitions with no effective difference:

/* main.p */ DEFINE NEW SHARED QUERY q FOR customer FIELDS (cust-num name). . . . /* shared.p’s */ DEFINE SHARED QUERY q FOR customer FIELDS. DEFINE SHARED QUERY q FOR customer EXCEPT. DEFINE SHARED QUERY q FOR customer FIELDS (). DEFINE SHARED QUERY q FOR customer FIELDS (cust-num name).

If you define a NEW SHARED query with a field list and a matching SHARED query without a field list, or if you define a NEW SHARED query without a field list and a matching SHARED query with a field list, Progress raises the ERROR condition when you run the procedure file that contains the SHARED query.

CACHE n

Specifies the number of records of the query to hold in memory for a NO-LOCK query. Generally, caching more records produces better browse performance when accessing a database across a network. However, caching consumes both memory and CPU time for buffer management.

If you specify the CACHE option, the SCROLLING option is assumed. If a query is referenced in a DEFINE BROWSE statement, caching occurs by default. The default for a query involving only one table is 50 records. The default for a multi-table query is 30 records. If you specify CACHE 0 in the DEFINE QUERY statement, no caching occurs.

SCROLLING

Specifies that you can jump to a location within the list of records that satisfy the query by using the REPOSITION statement. If you do not use this option, you can use only the FIRST, NEXT, LAST, and PREV options of the GET statement to navigate within the list. Queries are faster if you do not use this option, but you must specify it to use the REPOSITION statement. For non-OpenEdge databases, if you do not specify SCROLLING, you can only move forward through the list of records using the FIRST and NEXT options of the GET statement.

RCODE-INFORMATION Note: This option is the default behavior (thus, it has no effect). It is supported only for backward compatibility.

Examples

The following example defines two queries, q-salesrep and q-cust. The first is opened in the main procedure block and is used to find all salesrep records. The q-cust query is used to find all customers associated with a salesrep. The results of the q-cust query are displayed in a browse widget. The q-cust query is reopened each time you find a new salesrep.

r-defqry.p

DEFINE QUERY q-salesrep FOR salesrep FIELDS (salesrep.sales-rep salesrep.rep-name salesrep.region salesrep.month-quota). DEFINE QUERY q-cust FOR customer FIELDS (customer.cust-num customer.name customer.phone). DEFINE BROWSE cust-brws QUERY q-cust DISPLAY customer.cust-num customer.name customer.phone WITH 5 DOWN TITLE "Customer Information". DEFINE BUTTON b_next LABEL "Next". DEFINE BUTTON b_quit LABEL "Quit" AUTO-ENDKEY. FORM salesrep.sales-rep salesrep.rep-name salesrep.region salesrep.month-quota WITH FRAME rep-info SIDE-LABELS TITLE "Sales Rep. Info". FORM b_next space(5) b_quit WITH FRAME butt-frame COLUMN 60. ON CHOOSE OF b_next DO: GET NEXT q-salesrep. IF NOT AVAILABLE(salesrep) THEN GET FIRST q-salesrep. RUN disp-rep. END. OPEN QUERY q-salesrep FOR EACH salesrep NO-LOCK. GET FIRST q-salesrep. RUN disp-rep. ENABLE cust-brws WITH FRAME cust-info. ENABLE ALL WITH FRAME butt-frame. WAIT-FOR WINDOW-CLOSE OF CURRENT-WINDOW. PROCEDURE disp-rep. DISPLAY salesrep.sales-rep salesrep.rep-name salesrep.region salesrep.month-quota WITH FRAME rep-info CENTERED SIDE-LABELS TITLE "Sales Rep. Info". OPEN QUERY q-cust FOR EACH customer OF salesrep NO-LOCK. END PROCEDURE.

The following example uses the RCODE-INFORMATION option of the DEFINE QUERY statement to extracts index information from a static query. If you run the example with the RCODE-INFORMATION option commented out, Progress reports a run time error.

r-rcdinf.p

/* r-rcdinf.p */ /* Extracts index information from a static query.*/ DEFINE QUERY q FOR customer RCODE-INFORMATION. DEFINE VARIABLE h AS HANDLE. h = QUERY q:HANDLE. OPEN QUERY q FOR EACH cust BY name. MESSAGE h:INDEX-INFORMATION.

Notes

You cannot define a SHARED or NEW SHARED query in a class definition (.cls) file. If you do, Progress generates a compilation error.
After you define a query, you must open it with the OPEN QUERY statement before you can fetch any records.
A SHARED query remains in scope for an instance of a persistent procedure until the instance is deleted. This is true even if the original procedure that defined the query as NEW SHARED goes out of scope while the procedure instance remains persistent.

If a trigger or internal procedure of a persistent procedure executes an external subprocedure that defines a SHARED query, Progress includes the persistent procedure in the resolution of the corresponding NEW SHARED query as though the procedure were on the procedure call stack.

Specifying a field list (field-list) for bufname can increase the performance of remote (network) queries substantially over specifying bufname alone. For more information, see OpenEdge Development: Progress 4GL Handbook .
If you reference an unfetched database field in a query at run time, Progress raises the ERROR condition. Progress does not perform a compile-time check to ensure that the field is fetched because the compiler cannot reliably determine how a particular record will be read (that is, whether it is retrieved using a FIND statement, retrieved with or without a field list, including additional fields to satisfy join conditions, etc.).
Unlike with block record retrieval operations that include record updates and deletes (FOR EACH, etc.), field lists generally enhance query performance even for queries whose rows you plan to update. Queries generate complete result lists, with or without field lists, before any updates to individual rows are applied.
You can specify the Field List Disable (-fldisable) startup parameter to cancel field list retrieval and force Progress to retrieve complete records. This is a run-time client session parameter that is especially useful for deployed applications whose database triggers are later redefined to reference unfetched fields (raising the ERROR condition). Using -fldisable provides a workaround that allows the application to run (although more slowly) until the application can be fixed.
You cannot specify field lists in an OPEN QUERY statement.
In a shared query, the shared buffers must be specified in the same order across all the shared queries and in the OPEN QUERY statement.

See also

CLOSE QUERY statement, CURRENT-RESULT-ROW function, DEFINE BROWSE statement, GET statement, NUM-RESULTS function, OPEN QUERY statement, REPOSITION statement, RUN statement